Burgeon 解决表空间不足
本文档记录了 Burgeon 项目中涉及表空间操作的相关 SQL,包括查询数据文件、添加数据文件以及查看表空间使用详情等。
一、查询数据文件信息
SELECT * FROM dba_data_files;
二、添加数据文件(自动扩展)
ALTER TABLESPACE bosnds3 ADD DATAFILE '/data/oradata/orcl/bosnds3_03.DBFF' SIZE 10M AUTOEXTEND ON;
三、查询表空间使用详情
SELECT d.tablespace_name NAME,
d.status STATUS,
file_cnt,
auto_file_cnt,
max_create,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.90') size_mb,
TO_CHAR(NVL(a.total - NVL(f.bytes, 0), 0) / 1024 / 1024, '99999999.99') use_mb,
ROUND((NVL(a.bytes, 0) - NVL(a.total - NVL(f.bytes, 0), 0)) / 1024 / 1024, 2) free_mb,
TO_CHAR(NVL((a.total - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') used_lv
FROM sys.dba_tablespaces d,
(SELECT a.tablespace_name,
SUM((DECODE(a.autoextensible, 'YES',
CASE
WHEN a.autoextensible = 'YES' AND a.maxbytes >= a.bytes THEN a.maxbytes
ELSE a.bytes
END, 0) + DECODE(autoextensible, 'NO', a.bytes, 0))) bytes,
COUNT(1) file_cnt,
COUNT(CASE WHEN a.autoextensible = 'YES' THEN 1 END) auto_file_cnt,
MAX(b.creation_time) max_create,
SUM(a.bytes) total
FROM dba_data_files a, v$datafile b
WHERE a.file_id = b.file#
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY');